package org.fastsyncer.connector.database;

import java.sql.PreparedStatement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.fastsyncer.common.entity.DataSourceConfig;
import org.fastsyncer.common.entity.DataSourceConnector;
import org.fastsyncer.connector.common.DatabaseConstant;
import org.fastsyncer.connector.framework.Connector;
import org.fastsyncer.connector.util.DatabaseUtil;
import org.springframework.jdbc.core.JdbcTemplate;

public class DatabaseConnector implements Connector, Database {

	private static DatabaseConnector instance = null;

	/**
	 * 不建议直接new实例,建议使用DatabaseConnector.getInstance()替换
	 */
	@Deprecated
	public DatabaseConnector() {
	}

	/**
	 * 建议使用该方法
	 * 
	 * @return DatabaseConnector
	 */
	public static DatabaseConnector getInstance() {
		if (instance == null) {
			synchronized (DatabaseConnector.class) {
				instance = new DatabaseConnector();
			}
		}
		return instance;
	}

	@Override
	public JdbcTemplate getJdbcTemplate(DataSourceConfig config) {
		return DatabaseUtil.getJdbcTemplate(config);
	}

	@Override
	public DataSourceConnector getDataSourceConnector(DataSourceConfig config) {
		JdbcTemplate jdbcTemplate = DatabaseUtil.getJdbcTemplate(config);
		// 只查询元信息
		String metaSQL = "SELECT * FROM " + config.getTable().toUpperCase();
		String countSQL = metaSQL;
		switch (config.getDriverClassName()) {
		case DatabaseConstant.DB_MYSQL_DRIVER_CLASSNAME:
			// Mysql
			metaSQL += DatabaseConstant.DB_MYSQL_PAGE_SQL_NULL;
			break;
		case DatabaseConstant.DB_ORACLE_DRIVER_CLASSNAME:
			// Oracle
			metaSQL += DatabaseConstant.DB_ORACLE_PAGE_SQL_NULL;
			break;
		default:
			break;
		}

		DataSourceConnector connector = DatabaseUtil.getDataSourceConnector(jdbcTemplate, metaSQL, countSQL);
		connector.setConfig(config);
		return connector;
	}

	@Override
	public DataSourceConnector getDataSourceConnector(DataSourceConfig config, String queryFilter) {
		JdbcTemplate jdbcTemplate = DatabaseUtil.getJdbcTemplate(config);
		// 查询元信息
		String metaSQL = "SELECT * FROM " + config.getTable().toUpperCase();
		//求数据总量
		String countSQL = metaSQL + queryFilter;
		
		switch (config.getDriverClassName()) {
		case DatabaseConstant.DB_MYSQL_DRIVER_CLASSNAME:
			// Mysql
			metaSQL += DatabaseConstant.DB_MYSQL_PAGE_SQL_NULL;
			break;
		case DatabaseConstant.DB_ORACLE_DRIVER_CLASSNAME:
			// Oracle
			metaSQL += DatabaseConstant.DB_ORACLE_PAGE_SQL_NULL;
			break;
		default:
			break;
		}
		
		DataSourceConnector connector = DatabaseUtil.getDataSourceConnector(jdbcTemplate, metaSQL, countSQL);
		connector.setConfig(config);
		return connector;
	}

	@Override
	public String getJdbcSQL(String opertion, DataSourceConfig config, List<HashMap<String, Object>> fileds,
			String queryFilter) {
		String table = config.getTable().toUpperCase();
		String sql = "";
		switch (opertion) {
		case DatabaseConstant.OPERTION_SQL_QUERY:
			String query = "";
			for (HashMap<String, Object> obj : fileds) {
				String f = (String) obj.get("name");
				query += "_T." + f + ", ";
			}
			query = query.substring(0, query.lastIndexOf(","));
			sql = "SELECT " + query + " FROM " + table + " _T";
			// 解析查询条件
			sql += StringUtils.isNotBlank(queryFilter) ? queryFilter : "";
			// 分页语句
			sql = this.getPageSQL(config, sql);
			break;
		case DatabaseConstant.OPERTION_SQL_INSERT:
			String insertFileds = "";
			String insertValues = "";
			for (HashMap<String, Object> obj : fileds) {
				String f = (String) obj.get("name");
				insertFileds += table + "." + f + ", ";
				insertValues += "?, ";
			}
			insertFileds = insertFileds.substring(0, insertFileds.lastIndexOf(","));
			insertValues = insertValues.substring(0, insertValues.lastIndexOf(","));
			sql = "INSERT INTO " + table + "(" + insertFileds + ") VALUES (" + insertValues + ")";
			break;
		case DatabaseConstant.OPERTION_SQL_UPDATE:
			String update = "UPDATE " + config.getTable().toUpperCase() + " SET ";
			for (HashMap<String, Object> obj : fileds) {
				String f = (String) obj.get("name");
				update += table + "." + f + "=?,";
			}
			update = update.substring(0, update.lastIndexOf(","));
			sql += update + " WHERE " + table + "." + config.getPrimaryKey().toUpperCase() + "=?";
			break;
		case DatabaseConstant.OPERTION_SQL_DELETE:
			String delete = "DELETE FROM " + config.getTable().toUpperCase();
			sql += delete + " WHERE " + table + "." + config.getPrimaryKey().toUpperCase() + "=?";
			break;
		default:
			break;
		}
		return sql;
	}

	/**
	 * 解析查询条件
	 * 
	 * @param query
	 * @return
	 */
	public String getQuery(HashMap<String, List<HashMap<String, String>>> query) {
		if (query != null && query.size() > 0) {
			String conditionCMD = "";
			// 拼接SQL
			String addSql = this.getOperSQL(DatabaseConstant.OPERTION_SQL_QUERY_AND,
					query.get(DatabaseConstant.OPERTION_SQL_QUERY_AND));
			conditionCMD += addSql;

			// 拼接SQL
			String orSql = this.getOperSQL(DatabaseConstant.OPERTION_SQL_QUERY_OR,
					query.get(DatabaseConstant.OPERTION_SQL_QUERY_OR));
			// 如果已有Add条件
			conditionCMD += StringUtils.isNotBlank(addSql) && StringUtils.isNotBlank(orSql) ? " OR " + orSql : orSql;

			return StringUtils.isNotBlank(conditionCMD) ? " WHERE " + conditionCMD : "";
		}
		return "";
	}

	/**
	 * 根据过滤条件获取查询SQL
	 * 
	 * @param operator
	 * @param list
	 * @return
	 */
	private String getOperSQL(String operator, List<HashMap<String, String>> list) {
		String sql = "";
		if (list != null && list.size() > 0) {
			sql += "(";
			int size = list.size();
			for (int i = 0; i < size; i++) {
				HashMap<String, String> c = list.get(i);
				String oper = c.get("operator");
				sql += c.get("name") + DatabaseConstant.OPERS.get(oper) + "'" + c.get("value") + "'";
				sql += i < size - 1 ? " " + operator + " " : "";
			}
			sql += ")";
		}
		return sql;
	}

	/**
	 * 根据数据源类型获取分页SQL语句
	 * 
	 * @param config
	 * @return
	 */
	public String getPageSQL(DataSourceConfig config, String sql) {
		String driver = config.getDriverClassName().toLowerCase();
		// Mysql 分页查询
		if (driver.equalsIgnoreCase(DatabaseConstant.DB_MYSQL_DRIVER_CLASSNAME)) {
			return sql += DatabaseConstant.DB_MYSQL_PAGE_SQL;
		}
		// Oracle 分页查询
		if (driver.equalsIgnoreCase(DatabaseConstant.DB_ORACLE_DRIVER_CLASSNAME)) {
			return DatabaseConstant.DB_ORACLE_PAGE_SQL_START + sql + DatabaseConstant.DB_ORACLE_PAGE_SQL_END;
		}
		return "";
	}

	@Override
	public void batchRowsSetter(PreparedStatement ps, List<HashMap<String, Object>> fileds, Map<String, Object> row) {
		int filedSize = fileds.size();
		for (int i = 0; i < filedSize; i++) {
			// 取出字段和对应值
			HashMap<String, Object> filed = fileds.get(i);
			int type = Integer.parseInt(filed.get("type").toString());
			Object name = row.get(filed.get("name"));
			DatabaseUtil.preparedStatementSetter(ps, i + 1, type, name);
		}
	}
	
}
